package com.csi.okr.mobile.department.service.impl;

import com.alibaba.fastjson.JSON;
import com.csi.okr.common.util.JsonUtil;
import com.csi.okr.common.util.Pagination;
import com.csi.okr.mobile.common.dao.*;
import com.csi.okr.mobile.common.entity.VOkrEmplOrg;
import com.csi.okr.mobile.common.entity.VOkrOrg;
import com.csi.okr.mobile.department.service.DeptService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class DeptServiceImpl implements DeptService {

    @Resource
    private VOkrEmplDao emplDao;
    @Resource
    private VOkrEmplOrgDao emplOrgDao;
    @Resource
    private VOkrOrgDao orgDao;
    @Resource
    private VOkrPostDao postDao;
    @Resource
    private VOkrPostSeqDao postSeqDao;
    @Resource
    private TOkrObjectivesDao objDao;

    @Override
    public Object getOrgTree(String code) {

        String sql = "SELECT V3.ORG_UUID FROM V_OKR_EMPL V1,V_OKR_EMPL_ORG V2,V_OKR_ORG V3 WHERE V1.EMPL_CODE = V2.EMPL_CODE AND V2.ORG_CODE = V3.ORG_CODE AND V1.EMPL_CODE = '" + code + "'";
        List<Map<String, Object>> orgCode = orgDao.excuteSql(sql);

        List<List<VOkrOrg>> data = new ArrayList<List<VOkrOrg>>();

        for (Map<String, Object> org : orgCode) {

            Map<String, Object> map = new HashMap<String, Object>();
            //中软国际UUID
            map.put("orgUuid", org.get("ORG_UUID"));
            //查询中软国际下级组织
            List<VOkrOrg> topOrg = orgDao.findByProperty(map);
            data.add(topOrg);
        }

        //序列化
        String format = JSON.toJSONString(data);
        //正则替换掉原有的key 为了调用前端组织树的js方法
        format = format.replaceAll("childOrg", "list").replaceAll("orgZhSname", "name").replaceAll("orgCode", "url");
        return format;
    }

    @Override
    public Object getSubsetOrg(String orgCode, String emplCode, String year, String month, String quarter) {
        String sql_empl_org = " FROM VOkrEmplOrg V where V.emplCode = '" + emplCode + "' and V.orgCode = '" + orgCode + "'";
        VOkrEmplOrg ep = emplOrgDao.get(sql_empl_org);

        if (ep == null)
            return null;

        //SQL语句 查询符合条件数据
        String sql = "SELECT DISTINCT T1.PERSON_LIABLE_NAME,T1.PERSON_LIABLE_CODE,V3.POST_ZH_SNAME,V4.POSTSEQ_NAME,V3.SORT_ID SORT_V3,V4.SORT_ID SORT_V4 FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE";
        //WHERE 条件
        sql += " WHERE T1.OWNER_DEPTCODE = '" + orgCode + "' AND (	T1.PERSON_LIABLE_CODE = '" + emplCode + "' OR ( T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '" + emplCode + "')";
        //全公司透明
        sql += "OR (T1.IS_VISIBLE_OVERALL = 1)";

        if (ep.getIsLeader() != null && ep.getIsLeader().equals(1)) {
            //是否上级可见
            sql += "OR (T1.IS_VISIBLE_SUPERIOR = 1)";
        } else if (ep.getIsOkrCheck() != null && ep.getIsOkrCheck().equals(1)) {
            //是否上级可见
            sql += "OR (T1.IS_VISIBLE_SUPERIOR = 1)";
        } else {
            //是否下级可见
            sql += "OR (T1.IS_VISIBLE_SUBORDINATE = 1)";
        }
        //SQL语句反括号 不可去掉
        sql += ")";

        if (month != null && !"".equals(month) && !"0".equals(month)) {
            sql += " AND T1.O_MONTH = '" + month + "'";
        }

        if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
            sql += " AND T1.O_QUARTER = '" + quarter + "'";
        }

        if (year != null && !"".equals(year) && !"请选择".equals(year)) {
            if (Integer.parseInt(year) != 0) {
                sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
            }
        }

        sql += "ORDER BY V3.SORT_ID,V4.SORT_ID ASC";

        List<Map<String, Object>> data = objDao.excuteSql(sql);

        return data;

    }

    /**
     * 组装数据 - 部门负责人数据
     *
     * @param orgCode
     * @param emplCode
     * @return
     */
    private List<Map<String, Object>> getLeaderShow(String orgCode, String emplCode, String year, String month, String quarter) {
//		//查询符合条件管理序列数据 - 以O表为主表,关联查询视图,根据组织编号过滤,过滤重复
//		String sql_seq = "SELECT DISTINCT V4.POSTSEQ_UUID,V4.POSTSEQ_NAME,V4.POSTSEQ_CODE,V4.SORT_ID FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUPERIOR = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"')) ";
//		List<Map<String, Object>> map_seq = objDao.excuteSql(buildSql("V_OKR_POST_SEQ",sql_seq, year, month, quarter));
//		
//		for (Map<String, Object> seq : map_seq) {
//			//查询符合条件岗位数据 - 同上,根据组织编号,岗位序列编号,过滤重复信息
//			String sql_post = "SELECT DISTINCT V3.POST_CODE,V3.POST_ZH_SNAME,V3.SORT_ID FROM T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUPERIOR = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"'))  AND V4.POSTSEQ_UUID = '"+seq.get("POSTSEQ_UUID")+"'";
//			List<Map<String, Object>> map_post = objDao.excuteSql(buildSql("V_OKR_POST",sql_post, year, month, quarter));
//			
//			for (Map<String, Object> post : map_post) {
//				//查询符合条件员工信息 - 同上,根据组织编号,岗位序列编号,岗位编号过滤
//				String sql_empl = "SELECT DISTINCT T1.PERSON_LIABLE_CODE,T1.PERSON_LIABLE_NAME,V2.PERSON_SORT_ID FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUPERIOR = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"'))  AND V4.POSTSEQ_UUID = '"+seq.get("POSTSEQ_UUID")+"' AND V3.POST_CODE = '"+post.get("POST_CODE")+"'";
//				List<Map<String, Object>> map_empl = objDao.excuteSql(buildSql("T_OKR_OBJECTIVES",sql_empl, year, month, quarter));
//				//把符合条件数据填充入岗位内
//				post.put("empl", map_empl);
//			}
//			
//			//把符合条件信息填充如管理序列内
//			seq.put("post", map_post);
//		}
//		
//		//组装完成 返回数据
//		return map_seq;


        String sql = "SELECT DISTINCT T1.PERSON_LIABLE_NAME,T1.PERSON_LIABLE_CODE,V3.POST_ZH_SNAME,V4.POSTSEQ_NAME,V3.SORT_ID SORT_V3,V4.SORT_ID SORT_V4 FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + emplCode + "' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '" + emplCode + "') OR (T1.IS_VISIBLE_SUPERIOR = 1 AND T1.OWNER_DEPTCODE = '" + orgCode + "'))  ";

        if (month != null && !"".equals(month) && !"0".equals(month)) {
            sql += " AND T1.O_MONTH = '" + month + "'";
        }

        if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
            sql += " AND T1.O_QUARTER = '" + quarter + "'";
        }

        if (year != null && !"".equals(year) && !"请选择".equals(year)) {
            if (Integer.parseInt(year) != 0) {
                sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
            }
        }

        sql += "ORDER BY V3.SORT_ID,V4.SORT_ID ASC";

        List<Map<String, Object>> data = objDao.excuteSql(sql);

        return data;
    }

    /**
     * 组装数据 - 非部门负责人
     *
     * @param orgCode
     * @param emplCode
     * @return
     */
    private List<Map<String, Object>> getEmpLShow(String orgCode, String emplCode, String year, String month, String quarter) {
//		//查询符合条件管理序列数据 - 以O表为主表,关联查询视图,根据组织编号过滤,过滤重复,员工编号或下级可加
//		String sql_seq = "SELECT DISTINCT V4.POSTSEQ_UUID,V4.POSTSEQ_NAME,V4.POSTSEQ_CODE,V4.SORT_ID FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUBORDINATE = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"')) ";
//		List<Map<String, Object>> map_seq = objDao.excuteSql(buildSql("V_OKR_POST_SEQ",sql_seq, year, month, quarter));
//		
//		for (Map<String, Object> seq : map_seq) {
//			//查询符合条件岗位数据 - 同上,根据组织编号,岗位序列编号,过滤重复信息
//			String sql_post = "SELECT DISTINCT V3.POST_CODE,V3.POST_ZH_SNAME,V3.SORT_ID FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUBORDINATE = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"'))  AND V4.POSTSEQ_UUID = '"+seq.get("POSTSEQ_UUID")+"'";
//			List<Map<String, Object>> map_post = objDao.excuteSql(buildSql("V_OKR_POST",sql_post, year, month, quarter));
//			
//			for (Map<String, Object> post : map_post) {
//				//查询符合条件员工信息 - 同上,根据组织编号,岗位序列编号,岗位编号过滤
//				String sql_empl = "SELECT DISTINCT T1.PERSON_LIABLE_CODE,T1.PERSON_LIABLE_NAME,V2.PERSON_SORT_ID FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '"+emplCode+"') OR (T1.IS_VISIBLE_SUBORDINATE = 1 AND T1.OWNER_DEPTCODE = '"+orgCode+"'))  AND V4.POSTSEQ_UUID = '"+seq.get("POSTSEQ_UUID")+"' AND V3.POST_CODE = '"+post.get("POST_CODE")+"'"; 
//				List<Map<String, Object>> map_empl = objDao.excuteSql(buildSql("T_OKR_OBJECTIVES",sql_empl, year, month, quarter));
//				//把符合条件数据填充入岗位内
//				post.put("empl", map_empl);
//			}
//			
//			//把符合条件信息填充如管理序列内
//			seq.put("post", map_post);
//		}
//		
//		//组装完成 返回数据
//		return map_seq;

        String sql = "SELECT DISTINCT T1.PERSON_LIABLE_NAME,T1.PERSON_LIABLE_CODE,V3.POST_ZH_SNAME,V4.POSTSEQ_NAME,V3.SORT_ID SORT_V3,V4.SORT_ID SORT_V4 FROM  T_OKR_OBJECTIVES T1 LEFT JOIN V_OKR_EMPL_ORG V2 ON T1.PERSON_LIABLE_CODE = V2.EMPL_CODE LEFT JOIN V_OKR_POST V3 ON V2.POST_CODE = V3.POST_CODE LEFT JOIN V_OKR_POST_SEQ V4 ON V3.POSTSEQ_UUID = V4.POSTSEQ_UUID LEFT JOIN T_OKR_DESIGNATED_VISIBLE T5 ON T1.O_UNIQUE_ID_CODE = T5.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + emplCode + "' AND ((T1.IS_VISIBLE_DESIGNATED = 1 AND T5.DESIGNATEDE_PERSON_CODE = '" + emplCode + "') OR (T1.IS_VISIBLE_SUBORDINATE = 1 AND T1.OWNER_DEPTCODE = '" + orgCode + "')) ";

        if (month != null && !"".equals(month) && !"0".equals(month)) {
            sql += " AND T1.O_MONTH = '" + month + "'";
        }

        if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
            sql += " AND T1.O_QUARTER = '" + quarter + "'";
        }

        if (year != null && !"".equals(year) && !"请选择".equals(year)) {
            if (Integer.parseInt(year) != 0) {
                sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
            }
        }

        sql += "ORDER BY V3.SORT_ID,V4.SORT_ID ASC";

        List<Map<String, Object>> data = objDao.excuteSql(sql);

        return data;
    }

    //根据条件拼接SQL
    public String buildSql(String view, String sql, String year, String month, String quarter) {

        if (month != null && !"".equals(month) && !"0".equals(month)) {
            sql += " AND T1.O_MONTH = '" + month + "'";
        }

        if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
            sql += " AND T1.O_QUARTER = '" + quarter + "'";
        }

        if (year != null && !"".equals(year) && !"请选择".equals(year)) {
            if (Integer.parseInt(year) != 0) {
                sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
            }
        }

        if (view.equals("V_OKR_POST_SEQ")) {
            sql += " ORDER BY V4.SORT_ID ASC ";
        } else if (view.equals("V_OKR_POST")) {
            sql += " ORDER BY V3.SORT_ID ASC ";
        } else if (view.equals("T_OKR_OBJECTIVES")) {
            sql += " ORDER BY V2.PERSON_SORT_ID ASC ";
        }

        return sql;
    }

    @Override
    public Object getOkrByMemberCode(int pageIndex, int pageSize, String orgCode, String emplCode, String memberCode, String month, String year, String quarter, String state, String odefinition) {

        String sql = "";
        //是否是本人
        if (emplCode.equals(memberCode)) {
            sql = "SELECT T1.*, T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + emplCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "'";

        } else {
            //查询是否是负责人
            String sql_org = "SELECT * FROM V_OKR_EMPL_ORG WHERE ORG_CODE = '" + orgCode + "' AND EMPL_CODE = '" + emplCode + "'";
            Map<String, Object> org_empl = objDao.getUniqueBySql(sql_org);
            if (org_empl != null) {

                Integer isLeader = Integer.valueOf(org_empl.get("IS_LEADER").toString());
                Integer isOkrCheck = Integer.valueOf(org_empl.get("IS_OKR_CHECKER").toString());
                if (isLeader != null && isLeader.equals(1)) {
                    sql = "SELECT T1.*,T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + memberCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "' AND (T1.IS_VISIBLE_SUPERIOR = 1 OR (T1.IS_VISIBLE_DESIGNATED = 1 AND T2.DESIGNATEDE_PERSON_CODE = '" + emplCode + "') OR T1.IS_VISIBLE_OVERALL = 1)";
                } else if (isOkrCheck != null && isOkrCheck.equals(1)) {
                    //查询是否是负责人
                    String is_leader = "SELECT * FROM V_OKR_EMPL_ORG WHERE ORG_CODE = '" + orgCode + "' AND EMPL_CODE = '" + memberCode + "'";
                    Map<String, Object> empl_org_is_leader = objDao.getUniqueBySql(is_leader);
                    if(!empl_org_is_leader.isEmpty()){
                        Integer orgIsLeader = Integer.valueOf(empl_org_is_leader.get("IS_LEADER").toString());
                        if(orgIsLeader != null && orgIsLeader.equals(1)){
                            sql = "SELECT T1.*,T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + memberCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "' AND ( T1.IS_VISIBLE_OVERALL = 1 OR (T1.IS_VISIBLE_DESIGNATED = 1 AND T2.DESIGNATEDE_PERSON_CODE = '" + emplCode + "'))";
                        }else{
                            sql = "SELECT T1.*,T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + memberCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "' AND (T1.IS_VISIBLE_SUPERIOR = 1 OR T1.IS_VISIBLE_OVERALL = 1 OR (T1.IS_VISIBLE_DESIGNATED = 1 AND T2.DESIGNATEDE_PERSON_CODE = '" + emplCode + "'))";
                        }
                    }else{
                        sql = "SELECT T1.*,T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + memberCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "' AND ( T1.IS_VISIBLE_OVERALL = 1 OR (T1.IS_VISIBLE_DESIGNATED = 1 AND T2.DESIGNATEDE_PERSON_CODE = '" + emplCode + "'))";
                    }
                } else {
                    sql = "SELECT T1.*,T3.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_DESIGNATED_VISIBLE T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T3 ON T1.O_UNIQUE_ID_CODE = T3.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '" + memberCode + "' AND T1.OWNER_DEPTCODE = '" + orgCode + "' AND (T1.IS_VISIBLE_SUBORDINATE = 1 OR T1.IS_VISIBLE_OVERALL = 1 OR (T1.IS_VISIBLE_DESIGNATED = 1 AND T2.DESIGNATEDE_PERSON_CODE = '" + emplCode + "'))";
                }
            }
        }

        if ("".equals(sql)) {
            return -1;
        }

//        if (orgCode != null && !"".equals(orgCode)) {
//            sql += " AND T1.OWNER_DEPTCODE = '" + orgCode + "'";
//        }

        if (month != null && !"".equals(month) && !"0".equals(month)) {
            sql += " AND T1.O_MONTH = '" + month + "'";
        }

        if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
            sql += " AND T1.O_QUARTER = '" + quarter + "'";
        }

        if (state != null && !"".equals(state)) {
            //第一次进入
            if (state.equals("query")) {
                //暂无
            } else if (state.equals("oauth")) {
                sql += " AND T1.O_STATE != 'S5'";
            } else if ("0".equals(state)) {
                //暂无
            } else {
                sql += " AND T1.O_STATE = '" + state + "'";
            }
        }

        if (odefinition != null && !"".equals(odefinition)) {
            sql += " AND T1.O_DEFINITION = '" + odefinition + "'";
        }

        if (year != null && !"".equals(year) && !"请选择".equals(year)) {
            if (Integer.parseInt(year) != 0) {
                sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
            }
        }

        sql += " ORDER BY T1.O_YEAR DESC,T1.O_QUARTER DESC,T1.O_MONTH DESC,T1.O_CODE ASC";

        Pagination<Map<String, Object>> data = objDao.findSqlPagination(sql, pageIndex, pageSize);

        return JsonUtil.parseJson(data);
    }

}
